﻿using System;
using System.Collections;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI.WebControls;
using TTQT.TTQT_Code.Info;
using FileInfo = TTQT.TTQT_Code.Info.FileInfo;
using TTQT.TTQT_Code.Info;
namespace TTQT.TTQT_Code
{
    /// <summary>
    /// @kk
    /// This class is implemented as Singleton Pattern and used to access database.
    /// </summary>
    public class DataAccessObject
    {
        private static DataAccessObject _instance;

        public static DataAccessObject instance()
        {
            if(_instance == null)
            {
                _instance = new DataAccessObject();
                return _instance;
            }
            else
                return _instance;
            //return new DataAccessObject();
        }

        protected DataAccessObject()
        {
        }
        #region Cac method khac
        public static string GetDataString2(string sql)
        {
            string s = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {

                s = dr[0].ToString();
            }
            conn.Close();
            return s;

        }
        public int checkExistTable(string sTableName)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proCheckExistTable", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@TableName", sTableName);
            int result = -1;
            try
            {
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }
            return result;
        }

        public string getDetailProductCode(string sCode)
        {
            string result = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("getDetailProductCode", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Code", sCode);
            try
            {
                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }
            return result;
        }

        public void getFirstPath(string sTableName)
        {
            SqlDataReader dr = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetFirstPath", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@TableName", sTableName);
            try
            {
                conn.Open();
                dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string FirstPath = TTQTCommon.ToString(dr["FirstPath"]);
                    try
                    {
                        Directory.Delete(FirstPath, true);
                    }
                    catch (Exception ex)
                    {
                    }
                    string SecondPath = TTQTCommon.ToString(dr["SecondPath"]);
                    try
                    {
                        Directory.Delete(SecondPath, true);
                    }
                    catch (Exception ex)
                    {
                    }
                }
                conn.Close();
                dr.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
            }
        }


        public DataTable getAllCurrCode()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllCurrencyCode", conn);
            comm.CommandType = CommandType.StoredProcedure;

            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public DataTable getAllStatusForTTQT()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllStatusForTTQT", conn);
            comm.CommandType = CommandType.StoredProcedure;

            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public void DeleteTable(string sTableName)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proDeleteTable", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@TableName", sTableName);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
            }
        }


        public DataTable getDataByQuery(string sql)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.CommandType = CommandType.Text;
            comm.CommandText = sql;
            comm.Connection = conn;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
                // @ToDo: Log method here
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getDataByProcNonPara(string proc)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand(proc, conn);
            comm.CommandType = CommandType.StoredProcedure;

            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public string getStatusDiscription(string sStatus)
        {
            string result = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("select Discription from ttqt_TranStatus where code='" + sStatus + "'", conn);
            comm.CommandType = CommandType.Text;

            try
            {
                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        //Kiểm tra có tồn tại giao dịch trên SIBS ko, nếu có trả về true, nếu ko trả về false
        public bool getPrdcodFromSIBS()
        {
            bool result = false;
            try
            {
                DataTable tbl = new DataTable();
                string sqlString = "select PDPDTP, PDDESC from TFPAR2";
                OdbcConnection AS400Connection = new OdbcConnection();
                AS400Connection.ConnectionString = TTQTCommon.SIBSparaConnectionString;
                AS400Connection.Open();
                OdbcDataAdapter dataReader = new OdbcDataAdapter();
                dataReader.SelectCommand = new OdbcCommand(sqlString, AS400Connection);
                dataReader.Fill(tbl);
                if (tbl.Rows.Count > 0)
                {
                    SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                    //xoá dữ liệu cũ
                    String deleteCmd = "delete from TFPAR2";
                    SqlCommand delCommand = new SqlCommand(deleteCmd, conn);
                    delCommand.Connection.Open();
                    try
                    {
                        delCommand.ExecuteNonQuery();
                    }
                    catch (SqlException)
                    {
                        result = false;
                    }
                    delCommand.Connection.Close();

                    //thêm dữ liệu mới
                    String insertCmd = "insert into TFPAR2 (code, [desc]) values (@code, @desc)";
                    SqlCommand myCommand = new SqlCommand(insertCmd, conn);
                    myCommand.Parameters.Add(new SqlParameter("@code", SqlDbType.Char, 10));
                    myCommand.Parameters.Add(new SqlParameter("@desc", SqlDbType.Char, 50));

                    myCommand.Connection.Open();

                    foreach (DataRow row in tbl.Rows)
                    {
                        myCommand.Parameters["@code"].Value = row[0].ToString();
                        myCommand.Parameters["@desc"].Value = row[1].ToString();
                        try
                        {
                            myCommand.ExecuteNonQuery();
                        }
                        catch (SqlException)
                        {
                            result = false;
                        }
                    }
                    myCommand.Connection.Close();
                    result = true;
                }
                else
                    result = false;
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }

            return result;
        }
        #endregion

        #region User methods

        public int changePassword(string userName, string oldPass, string newPass)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proChangePassword", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@User_Name", userName);
            comm.Parameters.AddWithValue("@Old_Password", oldPass);
            comm.Parameters.AddWithValue("@New_Password", newPass);
            try
            {
                conn.Open();
                result = Convert.ToInt32(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Write log method here
            }
            return result;
        }

        // This method gets all Users for binding to a datagrid
        public DataTable getAllUser()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        /// <summary>
        /// Phuong thuc nay de lay cac user theo brn_code va ten vainhom
        /// </summary>
        /// <param name="brn_Code"></param>
        /// <param name="group_name">vai tro. neu dat bang "0" thi nghia la lay tat</param>
        /// <returns></returns>
        public DataTable GetUserByBranchAndGroupName(string brn_Code, string group_name)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetUserByBrn_CodeAndGroupName", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_Code", brn_Code);
            comm.Parameters.AddWithValue("@group_name", group_name);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string fdsa = ex.Message;
                // Writing log methods here.
            }
            return result;
        }
        /// <summary>
        /// Lấy các user cho admin chi nhánh quản lý
        /// </summary>
        /// <param name="branchCode"></param>
        /// <param name="user_Id"></param>
        /// <returns></returns>
        public DataTable User_GetForCN(string branchCode, string user_Id)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("User_GetForAdminCN2", conn);
            comm.Parameters.AddWithValue("@branchCode", branchCode);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string fdsa = ex.Message;
                // Writing log methods here.
            }
            // Loại bỏ người dùng hiện tại
            try
            {
                foreach (DataRow c in result.Rows)
                {
                    if (c["User_ID"].ToString().Equals(user_Id))
                    {
                        result.Rows.Remove(c);
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            return result;
        }
        /// <summary>
        /// Lấy các user cho admin TTCNTT
        /// </summary>
        /// <param name="user_Id"></param>
        /// <returns></returns>
        public DataTable User_GetForAdminTTCN(string user_Id)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("User_GetForAdminTTCN", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string fdsa = ex.Message;
                // Writing log methods here.
            }
            // Loại bỏ người dùng hiện tại
            try
            {
                foreach (DataRow c in result.Rows)
                {
                    if (c["User_ID"].ToString().Equals(user_Id))
                    {
                        result.Rows.Remove(c);
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            return result;
        }
        /// <summary>
        /// Phuong thuc nay dung de lay ca'c user theo brn_Code, Ten nhom loai tru user_Id
        /// </summary>
        /// <param name="brn_Code">Ma~ chi nhánh</param>
        /// <param name="group_name">Tên nhóm ví dụ TTVCNN hoặc KSVCNN hoặc TTVTTQT hoặc KSVTTQT</param>
        /// <param name="user_Id">Loại trừ người này</param>
        /// <returns></returns>
        public DataTable GetUserByBranchAndGroupName(string brn_Code, string group_name, string user_Id)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetUserByBrn_CodeAndGroupName", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_Code", brn_Code);
            comm.Parameters.AddWithValue("@group_name", group_name);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string fdsa = ex.Message;
                // Writing log methods here.
            }
            // Loại bỏ người dùng hiện tại
            try
            {
                foreach (DataRow c in result.Rows)
                {
                    if (c["User_ID"].ToString().Equals(user_Id))
                    {
                        result.Rows.Remove(c);
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            return result;
        }
        public DataTable getUserByBranch(string brn_Code)
        {
            //user_Type là group_Id. Để user_Type tức là lấy theo tất cả các group
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetUserByBrn_Code", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_Code", brn_Code);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string fdsa = ex.Message;
                // Writing log methods here.
            }
            return result;
        }
        public int kiemTraPhatSinhGiaoDich(string userName)
        {
            SqlConnection conn; //= new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm; //= new SqlCommand("proCheckLogin", conn);
            int result = 0;
            try
            {
                conn = new SqlConnection(TTQTCommon.DBConnectionString);
                conn.Open();
                comm = new SqlCommand("User_Before_Delete_kiemTraGD", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@userName", userName);
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), 0);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        /// This method gets One user for editting function depend on Userid
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo getUser(int id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@User_Id", id);
            UserInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new UserInfo();
                    obj.userId = TTQTCommon.ToInt32(dr["User_Id"], 0);
                    obj.userName = TTQTCommon.ToString(dr["User_Name"]);
                    obj.password = TTQTCommon.ToString(dr["User_Password"]);
                    obj.email = TTQTCommon.ToString(dr["User_Email"]);
                    obj.fullName = TTQTCommon.ToString(dr["User_FullName"]);
                    obj.birthday = TTQTCommon.ToString(dr["User_Birthday"]);
                    obj.address = TTQTCommon.ToString(dr["User_Address"]);
                    obj.phone = TTQTCommon.ToString(dr["User_Phone"]);
                    obj.groupId = TTQTCommon.ToInt32(dr["Group_Id"], 0);
                    obj.Brn_Code = TTQTCommon.ToString(dr["brn_Code"]);
                    obj.CMT = TTQTCommon.ToString(dr["CMT"]);
                    obj.MaKho = TTQTCommon.ToString(dr["MaKho"]);
                    obj.CapKho = TTQTCommon.ToString(dr["CapKho"]);
                    obj.SIBSID = TTQTCommon.ToString(dr["SIBSID"]);
                    obj.isSuperAdmin = TTQTCommon.ToString(dr["IsSuperAdmin"]);
                    obj.TenChiNhanh = TTQTCommon.ToString(dr["BranchName"]);
                    obj.Pgd = TTQTCommon.ToInt32(dr["PGD"], 0);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }

        public int addUser(UserInfo userInfo)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proAddUser", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_Name", userInfo.userName);
                comm.Parameters.AddWithValue("@User_Password", userInfo.password);
                comm.Parameters.AddWithValue("@User_Email", userInfo.email);
                comm.Parameters.AddWithValue("@User_FullName", userInfo.fullName);
                comm.Parameters.AddWithValue("@User_Birthday", userInfo.birthday);
                comm.Parameters.AddWithValue("@User_Address", userInfo.address);
                comm.Parameters.AddWithValue("@User_Phone", userInfo.phone);
                comm.Parameters.AddWithValue("@Group_Id", userInfo.groupId);
                comm.Parameters.AddWithValue("@Brn_Code", userInfo.Brn_Code);

                comm.Parameters.AddWithValue("@CMT", userInfo.CMT);
                comm.Parameters.AddWithValue("@MaKho", userInfo.MaKho);
                comm.Parameters.AddWithValue("@CapKho", userInfo.CapKho);
                comm.Parameters.AddWithValue("@SIBSID", userInfo.SIBSID);

                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }
        public int resetPassword(string userName, string newPass)
        {
            int r = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("resetPassword", conn);
                comm.CommandType = CommandType.StoredProcedure;
                
                comm.Parameters.AddWithValue("@User_Name",userName);
                comm.Parameters.AddWithValue("@User_Password", newPass);
                conn.Open();
                r = TTQTCommon.ToInt32(comm.ExecuteNonQuery(), -1);
                conn.Close();
                comm.Dispose();
                return r;
            }
            catch (Exception ex)
            {
                return -1;
                // Log methods here
            }
        }
        public int updateUser(UserInfo userInfo)
        {
            int r = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proUpdateUser", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_Id", userInfo.userId);
                comm.Parameters.AddWithValue("@User_Name", userInfo.userName);
                comm.Parameters.AddWithValue("@User_Password", userInfo.password);
                comm.Parameters.AddWithValue("@User_Email", userInfo.email);
                comm.Parameters.AddWithValue("@User_FullName", userInfo.fullName);
                comm.Parameters.AddWithValue("@User_Birthday", userInfo.birthday);
                comm.Parameters.AddWithValue("@User_Address", userInfo.address);
                comm.Parameters.AddWithValue("@User_Phone", userInfo.phone);
                comm.Parameters.AddWithValue("@Group_Id", userInfo.groupId);
                comm.Parameters.AddWithValue("@Brn_Code", userInfo.Brn_Code);

                comm.Parameters.AddWithValue("@CMT", userInfo.CMT);
                comm.Parameters.AddWithValue("@MaKho", userInfo.MaKho);
                comm.Parameters.AddWithValue("@CapKho", userInfo.CapKho);
                comm.Parameters.AddWithValue("@SIBSID", userInfo.SIBSID);
                conn.Open();
                r = TTQTCommon.ToInt32(comm.ExecuteNonQuery(), -1);
                conn.Close();
                comm.Dispose();
                return r;
            }
            catch (Exception ex)
            {
                return -1;
                // Log methods here
            }
        }

        public void deleteUser(int userId)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proDeleteUser_XoaKho", conn);
            try
            {
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_Id", userId);
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        // This method is for checking User when inserting new User
        public bool checkExistUser(string userName)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proCheckExistUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@userName", userName);
            int result = -1;
            try
            {
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Write log method here
            }
            return result > 0;
        }
        /// <summary>
        /// Khi người dùng đăng nhập thành công thì update lại phát.
        /// </summary>
        /// <param name="userName">Tên đăng nhập</param>
        /// <returns>1 nếu thành công, 0 nếu thất bại</returns>
        public int updateLastLogin(string userName)
        {
            SqlConnection conn; //= new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm; //= new SqlCommand("proCheckLogin", conn);
            int result = 0;
            try
            {
                conn = new SqlConnection(TTQTCommon.DBConnectionString);
                conn.Open();
                comm = new SqlCommand("proUpdateLastLogin", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@userName", userName);
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), 0);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        /// Khi người dùng thay đổi pass thành công thì update lại phát.
        /// </summary>
        /// <param name="userName">Tên đăng nhập</param>
        /// <returns>1 nếu thành công, 0 nếu thất bại</returns>
        public int updateLastChangePass(string userName)
        {
            SqlConnection conn; //= new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm; //= new SqlCommand("proCheckLogin", conn);
            int result = 0;
            try
            {
                conn = new SqlConnection(TTQTCommon.DBConnectionString);
                conn.Open();
                comm = new SqlCommand("proUpdateLastChangePass", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@userName", userName);
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), 0);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                return 0;
            }
            return result;
        }
        /// <summary>
        /// Da sua store
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int checkLogin(string userName, string password)
        {
            SqlConnection conn; //= new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm; //= new SqlCommand("proCheckLogin", conn);
            int result = -1;
            try
            {
                conn = new SqlConnection(TTQTCommon.DBConnectionString);
                conn.Open();
                comm = new SqlCommand("proCheckLogin", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@password", password);
            }
            catch (Exception ex)
            {
                return -100; // Khong ket noi duoc toi CSDL
            }
            try
            {
                result = Convert.ToInt32(comm.ExecuteScalar());
                if (result > 0)
                {
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
                string fdsa = err;
                // Write log method here
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return result;
        }
        /// <summary>
        /// Add Gán nhóm cho user với Group_Id và User_Id
        /// </summary>
        /// <param name="group_Id">Mã nhóm</param>
        /// <param name="user_Id">Mã user</param>
        public void addRoleToUser(int group_Id, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proAddRoleToUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@group_Id", group_Id);
            comm.Parameters.AddWithValue("@user_Id", user_Id);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Gán nhóm cho User với Group_Name và User_Id
        /// </summary>
        /// <param name="group_Name">Tên nhóm</param>
        /// <param name="user_Id">Mã user</param>
        public void addRoleToUser2(string group_Name, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proAddRoleToUser2", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@group_Name", group_Name);
            comm.Parameters.AddWithValue("@user_Id", user_Id);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
        }
        public void removeRoleFromUser(int group_Id, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proRemoveRoleFromUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@group_Id", group_Id);
            comm.Parameters.AddWithValue("@user_Id", user_Id);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        public void addCmdToUser(int cmd_id, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proAddCmdToUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@cmd_id", cmd_id);
            comm.Parameters.AddWithValue("@user_id", user_Id);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        public void removeCmdFromUser(int cmd_id, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proremoveCmdFromUser", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@cmd_id", cmd_id);
            comm.Parameters.AddWithValue("@user_Id", user_Id);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        #region Group methods

        public int addUserToGroup(int user_id, int group_id)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddUserToGroup", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@user_id", user_id);
                comm.Parameters.AddWithValue("@group_id", group_id);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
            return result;
        }

        public int removeUserFromGroup(int user_id, int group_id)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proRemoveUserFromGroup", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@user_id", user_id);
                comm.Parameters.AddWithValue("@group_id", group_id);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
            return result;
        }

        public int addGroup(GroupInfo groupInfo)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddGroup", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@Group_Name", groupInfo.GroupName);
                comm.Parameters.AddWithValue("@Group_Description", groupInfo.GroupDescription);
                comm.Parameters.AddWithValue("@CnDuocPhan", groupInfo.CnDuocPhan);
                comm.Parameters.AddWithValue("@CNDauMoi", groupInfo.CnDauMoi);
                comm.Parameters.AddWithValue("@CNKhongDauMoi", groupInfo.CnKhongDauMoi);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
            return result;
        }
        public DataTable getAllGroupForCN(string brnCode)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllGroupForCN_ByBrnCode", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brnCode", brnCode);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        /// <summary>
        /// Lấy ra các vai trò mà Admin CNN được phân
        /// </summary>
        /// <returns></returns>
        public DataTable getAllGroupForCN()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllGroupForCN", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        public DataTable getAllGroup()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllGroup", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        /// <summary>
        /// Lấy các nhóm để phân quyền cho người dùng. Nếu para= 
        /// @para=0 --> lay tat  
        /// @para=1 --> lay tat ca cac group CNN co' quyen phan  
        /// @para=2 --> lay tat ca cac group TTQT co quyen phan  
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable getGroupsToAssign(int para)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("group_getToAssign", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@para", para);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getGroupsByUserId(int user_id)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetGroupByUserId", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_id", user_id);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public GroupInfo getGroup(int groupId)
        {
            GroupInfo result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetGroup", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Group_ID", groupId);
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = new GroupInfo();
                    result.GroupId = TTQTCommon.ToInt32(dr["Group_ID"], 0);
                    result.GroupName = TTQTCommon.ToString(dr["Group_Name"]);
                    result.GroupDescription = TTQTCommon.ToString(dr["Group_Description"]);
                    result.CnDuocPhan = TTQTCommon.ToInt32(dr["CnDuocPhan"], 0);
                    result.CnDauMoi = TTQTCommon.ToInt32(dr["CNDauMoi"], 0);
                    result.CnKhongDauMoi= TTQTCommon.ToInt32(dr["CNKhongDauMoi"], 0);
                }
                if (dr != null) dr.Close();
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public void updateGroup(GroupInfo groupInfo)
        {
            try
            {
                SqlConnection conn = new SqlConnection((TTQTCommon.DBConnectionString));
                SqlCommand comm = new SqlCommand("proUpdateGroup", conn);
                comm.CommandType = CommandType.StoredProcedure;

                comm.Parameters.AddWithValue("@Group_Id", groupInfo.GroupId);
                comm.Parameters.AddWithValue("@Group_Name", groupInfo.GroupName);
                comm.Parameters.AddWithValue("@Group_Description", groupInfo.GroupDescription);
                comm.Parameters.AddWithValue("@CnDuocPhan", groupInfo.CnDuocPhan);
                comm.Parameters.AddWithValue("@CNDauMoi", groupInfo.CnDauMoi); 
                comm.Parameters.AddWithValue("@CNKhongDauMoi", groupInfo.CnKhongDauMoi);
                conn.Open();
                comm.ExecuteNonQuery();

                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
        }

        public void deleteGroup(int group_Id)
        {
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proDeleteGroup", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@Group_Id", group_Id);
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string ohShit = ex.ToString();
            }
        }

        // TODO:: Hiện tại phương thức này chạy sai vì group_Name là unicode
        public bool checkExistGroup(string group_Name)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proCheckExistGroup", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Group_Name", group_Name);
            int result = -1;
            try
            {
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Write log method here
            }
            return result > 0;
        }

        public string getAllGroupNamesByUserId(int user_id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("ttqt_groups_getAllGroupNamesToAString", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_id", user_id);
            string result = "";
            try
            {
                conn.Open();
                result = (comm.ExecuteScalar().ToString());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Write log method here
            }
            return result;
        }

        #endregion

        #region Phan quyen

        /// <summary>
        /// Khi người dùng chọn một menu chức năng thì kiểm tra xem người đó có quyền truy xuất menu đó không?
        /// Phương thức này kiểm tra với các menu được định nghĩa với đường dẫn đến file .aspx -- (Nhìn kỹ :trang asp.net cái này khác control)
        /// Nếu có quyền thì redirect đến trang đó, không thì redirect đến trang default.aspx
        /// </summary>
        /// <param name="userId"> Đây là id của người dùng (trong bảng ttqt_Users)</param>
        /// <param name="aspxFileName"> Đây là tên file .aspx lưu trong bảng ttqt_Cmds</param>
        public int checkPermission(int userId, string aspxFileName)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proCheckPermissionAsp2", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@User_Id", userId);
            comm.Parameters.AddWithValue("@cmd_Path ", aspxFileName);
            int result = -1;
            try
            {
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                //				
                //if(result>0)
                //{
                //LogInfo logInfo=new LogInfo(request.UserHostAddress,result,TTQTCommon.Log_DangNhap,"Đăng nhập thành công");
                //addLog(logInfo);
                //}
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string why = ex.ToString();
                // Write log method here
                return -2; // Bị mất kết nối CSDL đột ngột.
            }
            return result;
        }

        /// <summary>
        /// Khi người dùng chọn một menu chức năng thì kiểm tra xem người đó có quyền truy xuất menu đó không?
        /// Phương thức này kiểm tra với các menu được định nghĩa với đường dẫn đến file .ascx
        /// Nếu có quyền thì trả về mã cmd và trang default sẽ load control cho chức năng đó. Nếu không có quyền thì thông báo lỗi.
        /// </summary>
        /// <param name="userId"> Đây là id của người dùng (trong bảng ttqt_Users)</param>
        /// <param name="cmdId"> Đây là id của menu (trong bảng ttqt_Cmds)</param>
        public int checkPermission(int userId, int cmdId)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proCheckPermission2", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@User_Id", userId);
            comm.Parameters.AddWithValue("@Cmd_Id ", cmdId);
            int result = -1;
            try
            {
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                //				
                //				if(result>0)
                //				{
                //					LogInfo logInfo=new LogInfo(request.UserHostAddress,result,TTQTCommon.Log_DangNhap,"Đăng nhập thành công");
                //					addLog(logInfo);
                //				}

                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string why = ex.ToString();
                // Write log method here
            }
            return result;
        }


        public void addCmdToGroup(int groupId, int cmdId)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proAddCmdToGroup", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@group_id", groupId);
            comm.Parameters.AddWithValue("@cmd_id", cmdId);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void deleteCmdFromGroup(int groupId, int cmdId)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proDeleteCmdFromGroup", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@group_id", groupId);
            comm.Parameters.AddWithValue("@cmd_id", cmdId);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion
        #region Cmd methods

        public CmdInfo getCmd(int cmd_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCmd", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@cmd_Id", cmd_Id);
            CmdInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new CmdInfo();
                    obj.Cmd_Id = TTQTCommon.ToInt32(dr["Cmd_Id"], 0);
                    obj.Cmd_Name = TTQTCommon.ToString(dr["Cmd_Name"]);
                    obj.Cmd_Parent_Id = TTQTCommon.ToInt32(dr["Cmd_Parent_Id"], 0);
                    obj.Cmd_Path = TTQTCommon.ToString(dr["Cmd_Path"]);
                    obj.Display = TTQTCommon.ToInt32(dr["display"], -1);
                    obj.OrderCode = TTQTCommon.ToString(dr["OrderCode"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }

        public int addCmd(CmdInfo cmdInfo)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddCmd", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@cmd_Parent_id", cmdInfo.Cmd_Parent_Id);
                comm.Parameters.AddWithValue("@cmd_Name", cmdInfo.Cmd_Name);
                comm.Parameters.AddWithValue("@cmd_Path", cmdInfo.Cmd_Path);
                comm.Parameters.AddWithValue("@Display", cmdInfo.Display);
                comm.Parameters.AddWithValue("@OrderCode", cmdInfo.OrderCode);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
            return result;
        }

        public void updateCmd(CmdInfo cmdInfo)
        {
            try
            {
                SqlConnection conn = new SqlConnection((TTQTCommon.DBConnectionString));
                SqlCommand comm = new SqlCommand("proUpdateCmd", conn);
                comm.CommandType = CommandType.StoredProcedure;

                comm.Parameters.AddWithValue("@Cmd_Id", cmdInfo.Cmd_Id);
                comm.Parameters.AddWithValue("@cmd_Parent_id", cmdInfo.Cmd_Parent_Id);
                comm.Parameters.AddWithValue("@cmd_Name", cmdInfo.Cmd_Name);
                comm.Parameters.AddWithValue("@cmd_Path", cmdInfo.Cmd_Path);
                comm.Parameters.AddWithValue("@Display", cmdInfo.Display);
                comm.Parameters.AddWithValue("@OrderCode", cmdInfo.OrderCode);
                conn.Open();
                comm.ExecuteNonQuery();

                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
        }

        public void deleteCmd(int cmd_id)
        {
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proDeleteCmd", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@cmd_id", cmd_id);

                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
            }
        }

        public ArrayList getAllMenu()
        {
            ArrayList arr = new ArrayList();
            DataTable dtItems = GetByParentID(0);
            foreach (DataRow row in dtItems.Rows)
            {
                CmdInfo item = new CmdInfo();
                item.Cmd_Id = TTQTCommon.ToInt32(row["Cmd_ID"].ToString(), -1);
                item.Cmd_Name = row["Cmd_Name"].ToString();
                item.Cmd_Parent_Id = TTQTCommon.ToInt32(row["Cmd_Parent_Id"].ToString(), -1);
                item.Cmd_Path = row["Cmd_Path"].ToString();
                //item.Attributes.Add("Level", "0");
                //Add menu bố này vào danh sách
                arr.Add(item);
                //Lấy hết các menucon của ông bố này
                LoadChildCmd(arr, item);
            }
            return arr;
        }

        // Lấy menu được phân quyền theo user_id
        public ArrayList GetArrayCmdsByUser_Id(int user_Id)
        {
            ArrayList arr = new ArrayList();
            DataTable dtItems = GetByParentID(0);
            foreach (DataRow row in dtItems.Rows)
            {
                CmdInfo item = new CmdInfo();
                item.Cmd_Id = TTQTCommon.ToInt32(row["Cmd_ID"].ToString(), -1);
                item.Cmd_Name = row["Cmd_Name"].ToString();
                item.Cmd_Parent_Id = TTQTCommon.ToInt32(row["Cmd_Parent_Id"].ToString(), -1);
                item.Cmd_Path = row["Cmd_Path"].ToString();
                //item.Attributes.Add("Level", "0");
                //Add menu bố này vào danh sách
                arr.Add(item);
                //Lấy hết các menucon của ông bố này
                LoadChildCmd(arr, item);
            }
            // arr là một mảng chứa tất cả các CmdInfo trong CSDL
            // Bước tiếp theo loại bỏ các chức năng nhóm này không có quyền
            DataTable dtGroupRoles = getAuthorizedCmds(user_Id);
            // roles là chuỗi chứa các id của các quyền mà nhóm này có quyền truy xuất.
            // roles có dạng |1|3|5|7|8|9|12|24|

            string roles = "|";
            foreach (DataRow row in dtGroupRoles.Rows)
                roles += row["Cmd_ID"].ToString() + "|";
            int i = 0;
            // Duyệt lại danh sách các quyền đã lấy ban đầu, cái nào không có trong roles thì loại bỏ
            while (i < arr.Count)
            {
                if (roles.IndexOf("|" + ((CmdInfo)arr[i]).Cmd_Id + "|") < 0) arr.RemoveAt(i);
                else i += 1;
            }
            return arr;
        }

        private DataTable getAuthorizedCmds(int user_Id)
        {
            DataTable retVal = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            //SqlCommand comm = new SqlCommand("proGetAuthorizedCmds", conn);
            SqlCommand comm = new SqlCommand("proGetAuthorizedCmds", conn);
            comm.CommandType = CommandType.StoredProcedure;
            //comm.Parameters.AddWithValue("@Group_Id", groupID);
            comm.Parameters.AddWithValue("@user_Id", user_Id);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(retVal);
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }

        private void LoadChildCmd(ArrayList arr, CmdInfo curItem)
        {
            int curID = curItem.Cmd_Id;
            DataTable dtChild = GetByParentID(curID);
            foreach (DataRow row in dtChild.Rows)
            {
                CmdInfo childItem = new CmdInfo();
                childItem.Cmd_Id = TTQTCommon.ToInt32(row["Cmd_Id"].ToString(), -1);
                childItem.Cmd_Name = row["Cmd_Name"].ToString();
                childItem.Cmd_Parent_Id = TTQTCommon.ToInt32(row["Cmd_Parent_Id"].ToString(), -1);
                childItem.Cmd_Path = row["Cmd_Path"].ToString();
                arr.Add(childItem);
                // Gọi đệ quy (Nếu menu không có nhiều hơn 2 mức thì có thể xóa lệnh này. May quá! :)
                LoadChildCmd(arr, childItem);
            }
        }

        public DataTable getCmdsByGroupId(int groupID)
        {
            DataTable retVal = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCmdsByGroupId", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Group_Id", groupID);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(retVal);
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }
        public DataTable getCmdsByUserId(int user_id)
        {
            DataTable retVal = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCmdByUserId", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_id", user_id);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(retVal);
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }
        public DataTable GetByParentID(int parentID)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCmdByParentId", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Cmd_Parent_ID", parentID);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }


        public ArrayList GetAllCmds()
        {
            ArrayList arr = new ArrayList();
            DataTable dtItems = GetByParentID(0);
            foreach (DataRow row in dtItems.Rows)
            {
                CmdInfo item = new CmdInfo();
                item.Cmd_Id = TTQTCommon.ToInt32(row["Cmd_ID"].ToString(), -1);
                item.Cmd_Name = row["Cmd_Name"].ToString();
                item.Cmd_Parent_Id = TTQTCommon.ToInt32(row["Cmd_Parent_Id"].ToString(), -1);
                item.Cmd_Path = row["Cmd_Path"].ToString();
                //item.Attributes.Add("Level", "0");
                //Add menu bố này vào danh sách
                arr.Add(item);
                //Lấy hết các menucon của ông bố này
                LoadChildCmd(arr, item); //kkkk
            }
            return arr;
        }

        public void FillToListBox(ListItemCollection lstCmds)
        {
            try
            {
                lstCmds.Clear();
            }
            catch (Exception)
            {
            }

            // Lấy hết các menu bố
            DataTable dtItems = GetByParentID(0);
            foreach (DataRow row in dtItems.Rows)
            {
                ListItem item = new ListItem();
                item.Value = row["Cmd_ID"].ToString();
                item.Text = row["Cmd_Name"].ToString();
                item.Attributes.Add("Level", "0");
                //Add menu bố này vào danh sách
                lstCmds.Add(item);
                //Lấy hết các menucon của ông bố này
                LoadCmdItem(lstCmds, item);
            }
        }

        // Đây là một phương thức đệ quy để lấy hết các menu con của menu hiện tại.
        private void LoadCmdItem(ListItemCollection lstCmds, ListItem curItem)
        {
            int level = Convert.ToInt32(curItem.Attributes["Level"]);
            level += 1;
            int curID = Convert.ToInt32(curItem.Value);
            DataTable dtChild = GetByParentID(curID);
            foreach (DataRow row in dtChild.Rows)
            {
                ListItem childItem = new ListItem();
                childItem.Text = StringIndent(level) + row["Cmd_Name"].ToString();
                childItem.Value = row["Cmd_ID"].ToString();
                childItem.Attributes.Add("Level", level.ToString());
                lstCmds.Add(childItem);
                // Gọi đệ quy (Nếu menu không có nhiều hơn 2 mức thì có thể xóa lệnh này. May quá! :)
                LoadCmdItem(lstCmds, childItem);
            }
        }

        private string StringIndent(int level)
        {
            string result = string.Empty;
            for (int i = 0; i < level; i++)
                result += ".....";
            return result;
        }

        #endregion
        #region comment

        /*
        #region Branches

        public void UpdateTtqtBranch(string oldBrn_Code, Ttqt_BranchInfo info)
        {
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proUpdateTtqtBranch", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@oldBrn_Code", oldBrn_Code);
                comm.Parameters.AddWithValue("@Brn_Code", info.Brn_Code);
                comm.Parameters.AddWithValue("@User_Id", info.User_Id);
                comm.Parameters.AddWithValue("@Ttqt_CNN", info.Ttqt_CNN);
                comm.Parameters.AddWithValue("@Ttqt_CNTTQT", info.Ttqt_CNTTQT);

                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
        }

        public Ttqt_BranchInfo getTtqtBranchByCode(string brn_Code)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetTtqtBranchByCode", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_Code", brn_Code);
            Ttqt_BranchInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    obj = new Ttqt_BranchInfo();
                    obj.Brn_Id = TTQTCommon.ToInt32(dr["Brn_Id"], 0);
                    obj.Brn_Code = TTQTCommon.ToString(dr["Brn_Code"]);
                    obj.Brn_Code_Ttqt = TTQTCommon.ToString(dr["Brn_Code_Ttqt"]);
                    obj.Ttqt_CNN = TTQTCommon.ToInt32(dr["Ttqt_CNN"], 0);
                    obj.Ttqt_CNTTQT = TTQTCommon.ToInt32(dr["Ttqt_CNTTQT"], 0);
                    obj.User_Id = TTQTCommon.ToInt32(dr["User_Id"], 0);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }

        public BranchInfo getBranchByCode(string brn_Code)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetBranchByCode", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_Code", brn_Code);
            BranchInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new BranchInfo();
                    obj.Brn_Id = TTQTCommon.ToInt32(dr["Brn_Id"], 0);
                    obj.Brn_Code = TTQTCommon.ToString(dr["Brn_Code"]);
                    obj.Brn_Id_Name = TTQTCommon.ToString(dr["Brn_Id_Name"]);
                    obj.Brn_Name = TTQTCommon.ToString(dr["Brn_Name"]);
                    obj.Brn_Address = TTQTCommon.ToString(dr["Brn_Address"]);
                    obj.Brn_Phone = TTQTCommon.ToString(dr["Brn_Phone"]);
                    obj.Brn_Province = TTQTCommon.ToString(dr["Brn_Province"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }

        public int deleteBranch(string brn_Code)
        {
            int kq = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proDeleteTtqtBranch", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_Code", brn_Code);
                conn.Open();
                kq = comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
                return kq;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public int addTTQTBranch(Ttqt_BranchInfo obj)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddTtqtBranch", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_Code", obj.Brn_Code);
                comm.Parameters.AddWithValue("@brn_Code_Ttqt", obj.Brn_Code_Ttqt);
                comm.Parameters.AddWithValue("@ttqt_cnn", obj.Ttqt_CNN);
                comm.Parameters.AddWithValue("@ttqt_cnttqt", obj.Ttqt_CNTTQT);
                comm.Parameters.AddWithValue("@User_Id", obj.User_Id);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable GetAllBranches()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllBranches", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public ArrayList GetArrayDefinedBranches()
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetDefinedBranches", conn);
            comm.CommandType = CommandType.StoredProcedure;
            ArrayList arr = new ArrayList();
            BranchInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    obj = new BranchInfo();
                    obj.Brn_Id = TTQTCommon.ToInt32(dr["Brn_id"], 0);
                    obj.Brn_Code = TTQTCommon.ToString(dr["Brn_Code"]);
                    obj.Brn_Id_Name = TTQTCommon.ToString(dr["Brn_id_name"]);
                    obj.Ttqt_Cnn = TTQTCommon.ToString(dr["Ttqt_Cnn"]);
                    obj.Ttqt_Cnttqt = TTQTCommon.ToString(dr["Ttqt_Cnttqt"]);
                    obj.Brn_Name = TTQTCommon.ToString(dr["Brn_Name"]);
                    obj.Brn_Address = TTQTCommon.ToString(dr["Brn_Address"]);
                    obj.Brn_Phone = TTQTCommon.ToString(dr["Brn_Phone"]);
                    obj.Brn_Province = TTQTCommon.ToString(dr["Brn_Province"]);
                    arr.Add(obj);
                }
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
            }
            return arr;
        }

        public DataTable GetNotDefinedBranches()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetNotDefinedBranches", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getCNNbyTTQT(string brn_code_ttqt)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCNNbyTTQT", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@brn_code_ttqt", brn_code_ttqt);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getCNNbyKSV(string user_name_ksv)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCNNbyKSV", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_name_ksv", user_name_ksv);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }


        public DataTable getCNNbyTTV(string user_Name_ttv)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetCNNbyTTV", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_Name_ttv", user_Name_ttv);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getAllTTQTBranch()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllTTQTBranch", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable GetDefinedBranches()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetDefinedBranches", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        #endregion
        #region product methods

        public DataTable getAllProductCode()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllProductCode", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public DataTable getAllProductCodeByKSV(string user_name_ksv)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllProductCodeByKSV", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_name_ksv", user_name_ksv);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public DataTable getAllProductCodeByTTV(string user_name_ttv)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllProductCodeByTTV", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_name_ttv", user_name_ttv);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }
            return result;
        }

        public DataTable getTranStatusByCNN()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetTranStatusByCNN", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        public DataTable getTranStatusByTTQT()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetTranStatusByTTQT", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
            }
            return result;
        }

        #endregion
        
        #region ksv_branch_product

        public Ksv_Branch_Product_Info getKsv_Branch_Product_Info(string user_name)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proget_Ksv_Branch_Product", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_name", user_name);
            Ksv_Branch_Product_Info obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new Ksv_Branch_Product_Info();
                    obj.User_Name = TTQTCommon.ToString(dr["User_Name"]);
                    obj.Brn_code = TTQTCommon.ToString(dr["Brn_code"]);
                    obj.Product_code = TTQTCommon.ToString(dr["Product_code"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            return obj;
        }

        public Ksv_Branch_Product_Info getTtv_Branch_Product_Info(string user_name)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proget_Ttv_Branch_Product", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@user_name", user_name);
            Ksv_Branch_Product_Info obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new Ksv_Branch_Product_Info();
                    obj.User_Name = TTQTCommon.ToString(dr["User_Name"]);
                    obj.Brn_code = TTQTCommon.ToString(dr["Brn_code"]);
                    obj.Product_code = TTQTCommon.ToString(dr["Product_code"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
            }
            return obj;
        }

        public int updateKsv_Branch_Product(Ksv_Branch_Product_Info info)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proUpdate_Ksv_Branch_Product", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_name", info.User_Name);
                comm.Parameters.AddWithValue("@brn_code", info.Brn_code);
                comm.Parameters.AddWithValue("@product_code", info.Product_code);

                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
            return result;
        }

        public int updateTtv_Branch_Product(Ksv_Branch_Product_Info info)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proUpdate_Ttv_Branch_Product", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_name", info.User_Name);
                comm.Parameters.AddWithValue("@brn_code", info.Brn_code);
                comm.Parameters.AddWithValue("@product_code", info.Product_code);

                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
            return result;
        }

        public string checkBranchAndPrdcodForTTVTTQT(Ksv_Branch_Product_Info info, string brnCode, string prdCode)
        {
            string result = "";
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proCheck_BranchAndPrdcod_For_TTVTTQT", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@Username", info.User_Name);
                comm.Parameters.AddWithValue("@brn_code", brnCode);
                comm.Parameters.AddWithValue("@Prdcode", prdCode);

                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string err = ex.Message;
                // Log methods here
            }
            return result;
        }

        public string checkBranchForTTVTTQT(Ksv_Branch_Product_Info info, string brnCode)
        {
            string result = "";
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proCheck_Branch_For_TTVTTQT", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_name", info.User_Name);
                comm.Parameters.AddWithValue("@brn_code", brnCode);

                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
            return result;
        }

        public string checkPrdCodForTTVTTQT(Ksv_Branch_Product_Info info, string prdCod)
        {
            string result = "";
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proCheck_PrdCod_For_TTVTTQT", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@User_name", info.User_Name);
                comm.Parameters.AddWithValue("@PrdCode", prdCod);

                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
            return result;
        }

        #endregion
        
        
        #region Message
        public DataTable getMessageByTranCode(string sTranCode)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetMessageByTranCode", conn);
            comm.Parameters.AddWithValue("@TranCode", sTranCode);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public string addMessage(MessageInfo messageInfo)
        {
            string result = "";
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddMessage", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_code", messageInfo.Brn_Code);
                comm.Parameters.AddWithValue("@user_name", messageInfo.User_Name);
                comm.Parameters.AddWithValue("@trancode", messageInfo.TranCode);
                comm.Parameters.AddWithValue("@Content", messageInfo.Content);
                comm.Parameters.AddWithValue("@Time", messageInfo.DateTime);
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                }

                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        #endregion
        
        #region Assign
        /// <summary>
        /// Phương thức này dùng để lấy ra tên một KSV ở TTQT có quyền nhận loại giao dịch prd_Code từ brn_CNN để thêm vào bảng Ttqt_Assign
        /// Sử dụng khi KSV CNN gửi giao dịch.
        /// </summary>
        /// <param name="brn_CNN"></param>
        /// <param name="prd_Code"></param>
        /// <param name="brn_TTQT"></param>
        /// <returns></returns>
        public string gGetKSVTTQTForAutoAssign(string brn_CNN, string prd_Code, string brn_TTQT)
        {
            string result = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetKSVTTQTForAutoAssign", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_Code_CNN", brn_CNN);
                comm.Parameters.AddWithValue("@product_Code", prd_Code);
                comm.Parameters.AddWithValue("@brn_Code_TTQT", brn_TTQT);

                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        public string gGetTTVTTQTForAutoAssign(string brn_CNN, string prd_Code, string brn_TTQT)
        {
            string result = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetTTVTTQTForAutoAssign", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_Code_CNN", brn_CNN);
                comm.Parameters.AddWithValue("@product_Code", prd_Code);
                comm.Parameters.AddWithValue("@brn_Code_TTQT", brn_TTQT);

                conn.Open();
                result = TTQTCommon.ToString(comm.ExecuteScalar());
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        public string gGetAllKSVTTQTForAutoAssign(string brn_CNN, string prd_Code, string brn_TTQT)
        {
            string result = "";
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllKSVTTQTForAutoAssign", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@brn_Code_CNN", brn_CNN);
                comm.Parameters.AddWithValue("@product_Code", prd_Code);
                comm.Parameters.AddWithValue("@brn_Code_TTQT", brn_TTQT);

                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result += dr.GetValue(0).ToString() + ";";
                }
                conn.Close();
                comm.Dispose();
                if (result.Length > 0) result = result.Substring(0, result.Length - 1);
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        public AssignInfo getAssign(string tranCode)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAssign", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@tranCode", tranCode);
            AssignInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new AssignInfo();
                    obj.TranCode = TTQTCommon.ToString(dr["tranCode"]);
                    obj.User_Name_KSV = TTQTCommon.ToString(dr["user_Name_KSV"]);
                    obj.Arr_User_Name_TTV = TTQTCommon.ToString(dr["user_Name_TTV"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }
        public int addAssignK(AssignInfo info)
        {
            
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proAddAssignK", conn);

                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@tranCode", info.TranCode);
                comm.Parameters.AddWithValue("@user_Name_KSV", info.User_Name_KSV);
                comm.Parameters.AddWithValue("@user_Name_TTV", info.Arr_User_Name_TTV);
                conn.Open();
                //result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                result = comm.ExecuteNonQuery();
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                // Log methods here
            }
            return result;
        }
        /// <summary>
        /// Phương thức này tên là add nhưng nó bao gồm cả chức năng update(nếu tranCode đã tồn tại). Vì thế nếu muốn update cũng có thể gọi phương thức này
        /// </summary>
        public int addAssign(AssignInfo info)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            //kiểm tra đã có bản ghi trong database chưa
            string sql = "select tranCode from " + info.TableName + " where tranCode='" + info.TranCode + "'";
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.CommandType = CommandType.Text;
            int dem = 0;
            try
            {
                DataTable table = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(table);
                dem = table.Rows.Count;
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            if (dem == 0)//insert
            {
                comm.CommandText = "proAddAssign";
                comm.CommandType = CommandType.StoredProcedure;
                try
                {
                    //Add parameter for Command
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.AddWithValue("@tableName", info.TableName);
                    comm.Parameters.AddWithValue("@tranCode", info.TranCode);
                    comm.Parameters.AddWithValue("@user_Name_KSV", info.User_Name_KSV);
                    comm.Parameters.AddWithValue("@user_Name_TTV", info.Arr_User_Name_TTV);

                    conn.Open();
                    result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                    conn.Close();
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    string errStr = ex.ToString();
                    // Writing log methods here.
                }

            }
            else if (dem == 1)
            {
                comm.CommandText = "proUpdateAssign";
                comm.CommandType = CommandType.StoredProcedure;
                try
                {
                    //Add parameter for Command
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.AddWithValue("@tableName", info.TableName);
                    comm.Parameters.AddWithValue("@tranCode", info.TranCode);
                    comm.Parameters.AddWithValue("@user_Name_KSV", info.User_Name_KSV);
                    comm.Parameters.AddWithValue("@user_Name_TTV", info.Arr_User_Name_TTV);

                    conn.Open();
                    result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                    conn.Close();
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    string errStr = ex.ToString();
                    // Writing log methods here.
                }
            }
            else
                result = -1;
            return result;
        }

        public int addAssignForTTV(AssignInfo info)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            //kiểm tra đã có bản ghi trong database chưa
            string sql = "select tranCode from " + info.TableName + " where tranCode='" + info.TranCode + "'";
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.CommandType = CommandType.Text;
            int dem = 0;
            try
            {
                DataTable table = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(table);
                dem = table.Rows.Count;
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            if (dem == 0)//insert
            {
                comm.CommandText = "proAddAssignForTTV";
                comm.CommandType = CommandType.StoredProcedure;
                try
                {
                    //Add parameter for Command
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.AddWithValue("@tableName", info.TableName);
                    comm.Parameters.AddWithValue("@tranCode", info.TranCode);
                    comm.Parameters.AddWithValue("@user_Name_TTV", info.Arr_User_Name_TTV);

                    conn.Open();
                    result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                    conn.Close();
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    string errStr = ex.ToString();
                    // Writing log methods here.
                }
            }
            else if (dem == 1)
            {
                comm.CommandText = "proUpdateAssignForTTV";
                comm.CommandType = CommandType.StoredProcedure;
                try
                {
                    //Add parameter for Command
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.Parameters.AddWithValue("@tableName", info.TableName);
                    comm.Parameters.AddWithValue("@tranCode", info.TranCode);
                    comm.Parameters.AddWithValue("@user_Name_TTV", info.Arr_User_Name_TTV);

                    conn.Open();
                    result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                    conn.Close();
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    string errStr = ex.ToString();
                    // Writing log methods here.
                }
            }
            else
                result = -1;
            return result;
        }

        public int deleteAssign(string tranCode)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proDeleteAssign", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@tranCode", tranCode);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }
        public DataTable getAssignedTTV(string arr_User_Name_TTV)
        {
            //Phuong thuc này lấy tất cả các TTV theo KSV của chi nhánh TTQT để phân công thực hiện giao dịch.
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            string sql =
                @"select [User_ID], [User_Name], User_Fullname,User_Fullname+' ('+[User_Name]+') ' [UserNameAndFullName], User_Phone from ttqt_Users u where u.[User_Name] in (" +
                arr_User_Name_TTV + ")";
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.CommandType = CommandType.Text;
            comm.CommandText = sql;
            comm.Connection = conn;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
                // @ToDo: Log method here
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        #endregion
        */
        #endregion
        # region Loging

        public int addLog(LogInfo info)
        {
            int result = -1;
            try
            {
                SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
                SqlCommand comm = new SqlCommand("proCreateLog", conn);
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@IP", info.Ip);
                comm.Parameters.AddWithValue("@UserName", info.UserName);
                comm.Parameters.AddWithValue("@Type", info.Type);
                comm.Parameters.AddWithValue("@Content", info.Content);
                comm.Parameters.AddWithValue("@TranCode", info.TranCode);
                comm.Parameters.AddWithValue("@CreateTimes", info.CreateTimes);
                comm.Parameters.AddWithValue("@Brn_Code", info.Brn_Code);

                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getLogByYearMonth(string year, string month)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetLog", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Year", year);
            comm.Parameters.AddWithValue("@Month", month);

            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getAllLogType(string isCNN)
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllLogType", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@isCNN", isCNN);
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        #endregion
        #region Sysvar
        public SysvarInfo getSysVar(string paraName)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetSysVar", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@paraName", paraName);
            SysvarInfo obj = null;
            try
            {
                conn.Open();
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    obj = new SysvarInfo();
                    obj.ParaValue = TTQTCommon.ToString(dr["ParaValue"]);
                    obj.ParaDesc = TTQTCommon.ToString(dr["ParaDesc"]);
                    obj.ParaName = TTQTCommon.ToString(dr["ParaName"]);
                    conn.Close();
                    comm.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
            return obj;
        }
        public DataTable getAllSysVars()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetAllSysParameters", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public int updateSysVar(SysvarInfo info)
        {
            int result = -1;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proUpdateSysVar", conn);
            try
            {
                //Add parameter for Command
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@paraName", info.ParaName);
                comm.Parameters.AddWithValue("@ParaValue", info.ParaValue);
                comm.Parameters.AddWithValue("@ParaDesc", info.ParaDesc);
                conn.Open();
                result = TTQTCommon.ToInt32(comm.ExecuteScalar(), -1);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception ex)
            {
                string errStr = ex.ToString();
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getSysvarSIBSdata()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetSysvarSIBSdata", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }

        public DataTable getSysvarSIBSpara()
        {
            DataTable result = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("proGetSysvarSIBSpara", conn);
            comm.CommandType = CommandType.StoredProcedure;
            try
            {
                result = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(result);
                conn.Close();
                comm.Dispose();
            }
            catch (Exception)
            {
                // Writing log methods here.
            }
            return result;
        }
        #endregion
        #region Reports
       
        #endregion
        #region parCustomer
        public int addCustomerToSelect(string userName, string value)
        {
            int kq = -2;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("ParCustomer_AddToSelect", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@userName", userName);
            comm.Parameters.AddWithValue("@value", value);
            try
            {
                conn.Open();
                kq = Convert.ToInt32(comm.ExecuteScalar());
                conn.Close();
            }
            catch
            {
                kq = -2;
            }
            return kq;
        }
        #endregion
        #region parAgenBank
        public int addAgenBankToSelect(string userName, string value)
        {
            int kq = -2;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("ParAgentBank_AddToSelect", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@userName", userName);
            comm.Parameters.AddWithValue("@value", value);
            try
            {
                conn.Open();
                kq = Convert.ToInt32(comm.ExecuteScalar());
                conn.Close();
            }
            catch
            {
                kq = -2;
            }
            return kq;
        }
        #endregion
        #region MaKho
        public void MaKho_DongBo_SauKhiPhanQuyen(bool xoa, int group_Id, int user_Id)
        {
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            if(xoa)
            {
                SqlCommand comm = new SqlCommand("MaKho_Delete_SauKhiPhanQuyen", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@group_Id", group_Id);
                comm.Parameters.AddWithValue("@user_Id", user_Id);
                try
                {
                    conn.Open();
                    comm.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlCommand comm = new SqlCommand("MaKho_AddNew_SauKhiPhanQuyen", conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@group_Id", group_Id);
                comm.Parameters.AddWithValue("@user_Id", user_Id);
                

                try
                {
                    conn.Open();
                    comm.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
            
            
        }

        /// <summary>
        /// Thao.Nguyen created 20100805
        /// Lấy mã kho theo userID
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public string GetMaKhoByUserID(string userID)
        {
            SqlConnection sqlConn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand sqlComm = new SqlCommand("GetMaKhoByUserID", sqlConn);
            try
            {
                DataTable dt = new DataTable();
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.AddWithValue("@UserID", userID);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlComm);
                sqlConn.Open();
                sqlDataAdapter.Fill(dt);
                sqlConn.Close();
                sqlComm.Dispose();
                if (dt.Rows.Count > 0)
                {
                    string maKho = dt.Rows[0]["MaKho"].ToString();
                    return maKho;
                }
            }
            catch (Exception ex)
            {
                return string.Empty;
            }
            return string.Empty;
        }

        #endregion
        public DataTable MaKho_LayTheo_MaKhoCapTren(string maKhoCapTren)
        {
            DataTable retVal = null;
            SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
            SqlCommand comm = new SqlCommand("MaKho_LayTheo_MaKhoCapTren", conn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@MaKhoCapTren", maKhoCapTren);
            
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(retVal);
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }
    }
}


/// Ghi chú
/// Khi gọi Store mà một tham số là null thì sẽ không chạy được và gặp ngoại lệ là Tham số bị thiếu.
/// Vậy làm thế nào để có thể sử dụng một giá trị null cho một tham số khi gọi store?